﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace CardLogistics.DAL.PutOutCard
{
    /// <summary>
    /// 半成品/成品卡出库_数据访问类
    /// </summary>
    public class PutOutCardData
    {
        #region 查询
        /// <summary>
        /// 根据条件查询相应的表的字段
        /// </summary>
        /// <param name="StrWhere">查询条件</param>
        /// <param name="StrFields">要查询的字段</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetList(string StrWhere, string StrFields)
        {
            return CommonData.GetListOrderBy(StrWhere, "v_PutOutCard", StrFields, "ID", 0);
        }

        /// <summary>
        /// 根据编号查询基本信息
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetInfoById(string id)
        {
            return GetList(" ID = '" + id + "' ", "*");
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CardLogistics.Model.PutOutCard.PutOutCard GetModel(string rId)
        {
            CardLogistics.Model.PutOutCard.PutOutCard model = new CardLogistics.Model.PutOutCard.PutOutCard();
            DataRow row = GetInfoById(rId).Rows[0];
            if (row != null)
            {
                if (row["ID"] != null)
                {
                    model.ID = row["ID"].ToString();
                }
                if (row["PUTOUTCARDNO"] != null)
                {
                    model.PUTOUTCARDNO = row["PUTOUTCARDNO"].ToString();
                }
                if (row["PUTOUTCARDDATE"] != null && row["PUTOUTCARDDATE"].ToString() != "")
                {
                    model.PUTOUTCARDDATE = DateTime.Parse(row["PUTOUTCARDDATE"].ToString());
                }
                if (row["APPLYID"] != null)
                {
                    model.APPLYID = row["APPLYID"].ToString();
                }
                if (row["CARDNUMBER"] != null && row["CARDNUMBER"].ToString() != "")
                {
                    model.CARDNUMBER = decimal.Parse(row["CARDNUMBER"].ToString());
                }
                if (row["RECEIPTNO"] != null && row["RECEIPTNO"].ToString() != "")
                {
                    model.RECEIPTNO = row["RECEIPTNO"].ToString();
                }
                if (row["REMARKS"] != null)
                {
                    model.REMARKS = row["REMARKS"].ToString();
                }
                if (row["LASTUPDATEUSER"] != null)
                {
                    model.LASTUPDATEUSER = row["LASTUPDATEUSER"].ToString();
                }
                if (row["LASTUPDATEDATE"] != null && row["LASTUPDATEDATE"].ToString() != "")
                {
                    model.LASTUPDATEDATE = DateTime.Parse(row["LASTUPDATEDATE"].ToString());
                }
                if (row["ISDELETE"] != null && row["ISDELETE"].ToString() != "")
                {
                    model.ISDELETE = decimal.Parse(row["ISDELETE"].ToString());
                }
                if (row["ADDTIME"] != null && row["ADDTIME"].ToString() != "")
                {
                    model.ADDTIME = DateTime.Parse(row["ADDTIME"].ToString());
                }
                if (row["ISSUBMIT"] != null && row["ISSUBMIT"].ToString() != "")
                {
                    model.ISSUBMIT = decimal.Parse(row["ISSUBMIT"].ToString());
                }
                if (row["SUPPID"] != null)
                {
                    model.SUPPID = row["SUPPID"].ToString();
                }

                #region 便于查询字段
                if (row["ApplyNo"] != null && row["ApplyNo"].ToString() != "")
                {
                    model.ApplyNo = row["ApplyNo"].ToString();//申领单号
                }
                #endregion
            }
            return model;
        }
        #endregion

        #region 新增
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(CardLogistics.Model.PutOutCard.PutOutCard model)
        {
            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
				 	new OracleParameter("vPUTOUTCARDNO", OracleType.NVarChar),
					new OracleParameter("vPUTOUTCARDDATE", OracleType.DateTime),
					new OracleParameter("vAPPLYID", OracleType.NVarChar),
					new OracleParameter("vCARDNUMBER", OracleType.Number,4),
					new OracleParameter("vRECEIPTNO", OracleType.NVarChar,50),
					new OracleParameter("vREMARKS", OracleType.NVarChar,200),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
					new OracleParameter("vISSUBMIT", OracleType.Number,4)};
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = model.PUTOUTCARDNO;
                parameters[2].Value = model.PUTOUTCARDDATE;
                parameters[3].Value = model.APPLYID;
                parameters[4].Value = model.CARDNUMBER;
                parameters[5].Value = model.RECEIPTNO;
                parameters[6].Value = model.REMARKS;
                parameters[7].Value = model.LASTUPDATEUSER;
                parameters[8].Value = model.LASTUPDATEDATE;
                parameters[9].Value = model.ISDELETE;
                parameters[10].Value = model.ADDTIME;
                parameters[11].Value = model.ISSUBMIT;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutOutCard_Add", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 重载新增信息
        /// <summary>
        /// 重载新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(CardLogistics.Model.PutOutCard.PutOutCard model, string FileValue)
        {
            string StrOutPar = string.Empty;
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                StringBuilder strSql = new StringBuilder();

                try
                {
                    string gid = System.Guid.NewGuid().ToString();//主键Id
                    string[] split = FileValue.Split(';');//以分号分隔
                    int count = split.Length; //计算行数

                    strSql.Append(" begin  ");
                    strSql.Append(" delete from putoutcard_detail where PutOutCardId ='" + gid + "';");
                    for (int i = 0; i < count; i++)
                    {
                        string cardno = split[i].Split('|')[0];
                        string iniflag = split[i].Split('|')[1] == "否" ? "0" : "1";
                        string fgvamt = split[i].Split('|')[2];
                        string precharge = split[i].Split('|')[3];
                        string startcardno = split[i].Split('|')[4];
                        string endcardno = split[i].Split('|')[5];
                        string duenumber = split[i].Split('|')[6];
                        string actualnumber = split[i].Split('|')[7];
                        string missingcardno = split[i].Split('|')[8];//缺卡卡号

                        strSql.Append(" insert into putoutcard_detail");
                        strSql.Append(" (putoutcardid,cardno,iniflag,fgvamt,precharge,startcardno,endcardno,duenumber,actualnumber,missingcardno)");
                        strSql.Append(" values(");
                        strSql.Append("'" + gid + "',");
                        strSql.Append("" + cardno + "," + iniflag + "," + fgvamt + "," + precharge + ",'" + startcardno + "','" + endcardno + "',");
                        strSql.Append("" + duenumber + "," + actualnumber + "," + missingcardno + "");
                        strSql.Append(");");
                        #region 插入卡明细
                        strSql.Append("P_CARDRECORD_Add(" + startcardno + "," + endcardno + ",'0',0,'" + gid + "',");
                        strSql.Append("to_date('" + model.PUTOUTCARDDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),4," + i + ",0);");
                        #endregion

                    }
                    strSql.Append(" end; ");

                    if (!String.IsNullOrEmpty(strSql.ToString()))
                    {
                        cmd.CommandText = strSql.ToString();
                        val += cmd.ExecuteNonQuery();
                    }

                    OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
				 	new OracleParameter("vPUTOUTCARDNO", OracleType.NVarChar),
					new OracleParameter("vPUTOUTCARDDATE", OracleType.DateTime),
					new OracleParameter("vAPPLYID", OracleType.NVarChar),
					new OracleParameter("vCARDNUMBER", OracleType.Number,4),
					new OracleParameter("vRECEIPTNO", OracleType.NVarChar,50),
					new OracleParameter("vREMARKS", OracleType.NVarChar,200),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
					new OracleParameter("vISSUBMIT", OracleType.Number,4),
                    new OracleParameter("vID",OracleType.VarChar),
                    new OracleParameter("vSUPPID",OracleType.VarChar)};
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Value = model.PUTOUTCARDNO;
                    parameters[2].Value = model.PUTOUTCARDDATE;
                    parameters[3].Value = model.APPLYID;
                    parameters[4].Value = model.CARDNUMBER;
                    parameters[5].Value = model.RECEIPTNO;
                    parameters[6].Value = model.REMARKS;
                    parameters[7].Value = model.LASTUPDATEUSER;
                    parameters[8].Value = model.LASTUPDATEDATE;
                    parameters[9].Value = model.ISDELETE;
                    parameters[10].Value = model.ADDTIME;
                    parameters[11].Value = model.ISSUBMIT;
                    parameters[12].Value = gid;
                    parameters[13].Value = model.SUPPID;
                    StrOutPar = DbHelperSQL.RunProcedure("P_PutOutCard_Add", parameters, parameters[0]).ToString();
                    if (StrOutPar.Split(',')[0] == "ok")
                        tx.Commit();
                    else
                        tx.Rollback();
                    return StrOutPar;
                }
                catch (System.Data.OracleClient.OracleException e)
                {
                    tx.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(CardLogistics.Model.PutOutCard.PutOutCard model)
        {
            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("vID", OracleType.VarChar,50),
				   	new OracleParameter("vPUTOUTCARDNO", OracleType.NVarChar),
					new OracleParameter("vPUTOUTCARDDATE", OracleType.DateTime),
					new OracleParameter("vAPPLYID", OracleType.NVarChar),
					new OracleParameter("vCARDNUMBER", OracleType.Number,4),
					new OracleParameter("vRECEIPTNO", OracleType.NVarChar,50),
					new OracleParameter("vREMARKS", OracleType.NVarChar,200),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
					new OracleParameter("vISSUBMIT", OracleType.Number,4),
                    new OracleParameter("varOut", OracleType.VarChar,50)};
                parameters[0].Value = model.ID;
                parameters[1].Value = model.PUTOUTCARDNO;
                parameters[2].Value = model.PUTOUTCARDDATE;
                parameters[3].Value = model.APPLYID;
                parameters[4].Value = model.CARDNUMBER;
                parameters[5].Value = model.RECEIPTNO;
                parameters[6].Value = model.REMARKS;
                parameters[7].Value = model.LASTUPDATEUSER;
                parameters[8].Value = model.LASTUPDATEDATE;
                parameters[9].Value = model.ISDELETE;
                parameters[10].Value = model.ADDTIME;
                parameters[11].Value = model.ISSUBMIT;
                parameters[12].Direction = ParameterDirection.Output;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutOutCard_Update", parameters, parameters[12]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 重载修改信息
        /// <summary>
        /// 重载修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(CardLogistics.Model.PutOutCard.PutOutCard model, string FileValue)
        {
            string StrOutPar = string.Empty;
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                string[] split = FileValue.Split(';');//以分号分隔
                int count = split.Length; //计算行数
                StringBuilder strSql = new StringBuilder();
                try
                {
                    strSql.Append(" begin  ");
                    strSql.Append(" delete from putoutcard_detail where PutOutCardId ='" + model.ID + "';");//清空出库明细
                    strSql.Append(" update CARDRECORD set CPCHUSJ=null,CPCHUID=null where CPCHUID='" + model.ID + "';");//清空卡流水成品出库记录
                    for (int i = 0; i < count; i++)
                    {
                        string cardno = split[i].Split('|')[0];
                        string iniflag = split[i].Split('|')[1] == "否" ? "0" : "1";
                        string fgvamt = split[i].Split('|')[2];
                        string precharge = split[i].Split('|')[3];
                        string startcardno = split[i].Split('|')[4];
                        string endcardno = split[i].Split('|')[5];
                        string duenumber = split[i].Split('|')[6];
                        string actualnumber = split[i].Split('|')[7];
                        string missingcardno = split[i].Split('|')[8];//缺卡卡号

                        strSql.Append(" insert into putoutcard_detail");
                        strSql.Append(" (putoutcardid,cardno,iniflag,fgvamt,precharge,startcardno,endcardno,duenumber,actualnumber,missingcardno)");
                        strSql.Append(" values(");
                        strSql.Append("'" + model.ID + "',");
                        strSql.Append("" + cardno + "," + iniflag + "," + fgvamt + "," + precharge + ",'" + startcardno + "','" + endcardno + "',");
                        strSql.Append("" + duenumber + "," + actualnumber + "," + missingcardno + "");
                        strSql.Append(");");

                        #region 插入卡明细
                        strSql.Append("P_CARDRECORD_Add(" + startcardno + "," + endcardno + ",'0',0,'" + model.ID + "',");
                        strSql.Append("to_date('" + model.PUTOUTCARDDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),4," + i + ",0);");
                        #endregion

                    }
                    strSql.Append(" end; ");
                    if (!String.IsNullOrEmpty(strSql.ToString()))
                    {
                        cmd.CommandText = strSql.ToString();
                        val += cmd.ExecuteNonQuery();
                    }

                    OracleParameter[] parameters = {
                    new OracleParameter("vID", OracleType.VarChar,50),
				   	new OracleParameter("vPUTOUTCARDNO", OracleType.NVarChar),
					new OracleParameter("vPUTOUTCARDDATE", OracleType.DateTime),
					new OracleParameter("vAPPLYID", OracleType.NVarChar),
					new OracleParameter("vCARDNUMBER", OracleType.Number,4),
					new OracleParameter("vRECEIPTNO", OracleType.NVarChar,50),
					new OracleParameter("vREMARKS", OracleType.NVarChar,200),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
					new OracleParameter("vISSUBMIT", OracleType.Number,4),
                    new OracleParameter("varOut", OracleType.VarChar,50),
                    new OracleParameter("vSUPPID", OracleType.VarChar,50)};
                    parameters[0].Value = model.ID;
                    parameters[1].Value = model.PUTOUTCARDNO;
                    parameters[2].Value = model.PUTOUTCARDDATE;
                    parameters[3].Value = model.APPLYID;
                    parameters[4].Value = model.CARDNUMBER;
                    parameters[5].Value = model.RECEIPTNO;
                    parameters[6].Value = model.REMARKS;
                    parameters[7].Value = model.LASTUPDATEUSER;
                    parameters[8].Value = model.LASTUPDATEDATE;
                    parameters[9].Value = model.ISDELETE;
                    parameters[10].Value = model.ADDTIME;
                    parameters[11].Value = model.ISSUBMIT;
                    parameters[12].Direction = ParameterDirection.Output;
                    parameters[13].Value = model.SUPPID;
                    StrOutPar = DbHelperSQL.RunProcedure("P_PutOutCard_Update", parameters, parameters[12]).ToString();
                    if (StrOutPar.Split(',')[0] == "ok")
                        tx.Commit();
                    else
                        tx.Rollback();
                    return StrOutPar;
                }
                catch (System.Data.OracleClient.OracleException e)
                {
                    tx.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id">删除信息的ID</param>
        /// <returns>删除成功返回True。否则返回false</returns>
        public string Delete(string id)
        {
            try
            {
                OracleParameter[] parameters = {
                        new OracleParameter("varOut",  OracleType.VarChar,100),
					    new OracleParameter("vid",  OracleType.VarChar,50)
                };
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = id;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutOutCard_Delete", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        /// <summary>
        /// 根据申领单号、编辑时的ID 返回 盒号、开始卡号等信息
        /// </summary>
        /// <param name="appId">申领单主键</param>
        /// <param name="eId">编辑时的主键</param>
        /// <returns></returns>
        public DataTable GetBoxSupInfo(string eId,string appId)
        {
            StringBuilder sbInfo = new StringBuilder();
            sbInfo.Append(" select a.Id as zkId,a.CARDNO,a.Boxno,a.cardtypename,a.cardtypeid,a.suppliersname,a.suppid,a.ActuNumber,a.FlagName,a.Fgvamt,a.InitAmt,a.Endno,");
            sbInfo.Append(" case length(LENDCARDNO) when 8 then  lPad(to_number(LENDCARDNO+1),8,0) else lPad(to_number(a.Startno),8,0) end Startno,s.checkid");
            sbInfo.Append(" from v_putincarddetailedrpt a left join ");
            sbInfo.Append(" (select sum(ACTUALNUMBER) lACTUALNUMBER,CARDNO,max(ENDCARDNO) LENDCARDNO,min(issubmit) bissubmit from v_putoutcard_detail where issubmit<>4");
            if (eId != "")//编辑时排除本条记录的计算
                sbInfo.Append(" and PUTOUTCARDID <>'" + eId + "'");
            sbInfo.Append(" group by CARDNO) b on a.CARDNO=b.CARDNO");
            sbInfo.Append(" left join v_storage_check_card_detailed s on s.cardno=a.CARDNO");//关联验收入库表获取验收入库ID
            sbInfo.Append(" where a.ActuNumber-nvl(b.lACTUALNUMBER,0)>0");
            sbInfo.Append(" and a.cardtypeid in(select cardtypeid from v_PutOutApply where id='" + appId + "' )");//根据审批单的卡类型查询可用的入库信息
            if (eId == "")//新增时加过滤条件
            sbInfo.Append(" and a.CARDNO not in (select * from v_getretcardno )");//过滤已经退货的卡号
            sbInfo.Append(" and nvl(bissubmit,100)>3 and a.Issubmit=5");
            return DbHelperSQL.GetQueryResult(sbInfo.ToString());

        }
    }
}
